﻿<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <!--DXMETADATA start type="MetaCharset" --><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-8"><!--DXMETADATA end-->
    <meta http-equiv="X-UA-Compatible" value="IE=9" />

    <!--DXMETADATA start type="Literal" condition="helpversion:value=3" value="<meta name=""Microsoft.Help.SelfBranded"" content=""true"" />" --><!--DXMETADATA end-->
    <!--DXMETADATA start type="ItemTitle" format="<title>%%ProjectTitle%% - %%ItemTitle%%</title>" --><title>SpreadJS Documentation - Dynamic Array Formulas</title><!--DXMETADATA end-->
    <!--DXMETADATA start type="ItemTitle" format="<meta name=""Title"" content=""%%ProjectTitle%% - %%ItemTitleNoQuotes%%""/>" --><meta name="Title" content="SpreadJS Documentation - Dynamic Array Formulas"/><!--DXMETADATA end-->
    
    <!--DXMETADATA start type="PackageLink" packagename="jquery" filetype="script" firstlinkattributes="id=""mshs_support_script"""--><script src="template/packages/jquery/script/default/jquery-1.11.3.min.js" type="text/javascript" id="mshs_support_script"></script><!--DXMETADATA end-->
    <!--DXMETADATA start type="PackageLink" packagename="jquery-ui" filetype="script"--><script src="template/packages/jquery-ui/script/default/jquery-ui-1.11.4.min.js" type="text/javascript"></script><!--DXMETADATA end-->
    <!--DXMETADATA start type="PackageLink" packagename="jquery-ui" filetype="css"--><link rel="stylesheet" type="text/css" href="template/packages/jquery-ui/css/default/jquery-ui-1.11.4.css"></link><!--DXMETADATA end-->
    <!--DXMETADATA start type="TemplateSettingsJson" format="<script type=""text/javascript"">var Innovasys=(Innovasys||{});$.extend(true,Innovasys,{settings:{isHideBodyDuringLoadDisabled:true,dynamictoc:{isEnabled:true},inthistopic:{isSupported:true},dynamicstyles:{isDynamicWordWrapEnabled:true}}},{settings:%%TemplateSettingsJson%%});</script>" --><script type="text/javascript">var Innovasys=(Innovasys||{});$.extend(true,Innovasys,{settings:{isHideBodyDuringLoadDisabled:true,dynamictoc:{isEnabled:true},inthistopic:{isSupported:true},dynamicstyles:{isDynamicWordWrapEnabled:true}}},{settings:{"dynamictoc":{"initialNodeId":"n180","initialNodeContainer":"c0","isResizable":true},"inthistopic":{"isEnabled":true},"currentLocale":"-","isFrameless":true,"navigationKind":"inpage","versions":{"locale":{"currentId":"-"}}}});</script><!--DXMETADATA end-->
    <!--DXMETADATA start type="PackageLink" packagename="light" filetype="css"--><link rel="stylesheet" type="text/css" href="template/packages/light/css/dynamic-toc.css"></link><!--DXMETADATA end-->
    <!--DXMETADATA start type="Synopsis" StripHtmlTags="True" MaxLength="250" format="<meta name=""Description"" content=""%%Synopsis%%"" />"--><meta name="Description" content="SpreadJS provides extensive support for using dynamic array formulas in spreadsheets." /><!--DXMETADATA end-->
    
    <!--DXMETADATA start type="PackageLink" packagename="plugins-topics" filetype="css"--><link rel="stylesheet" type="text/css" href="template/packages/plugins-topics/css/default/jquery-plugins.css"></link><!--DXMETADATA end-->
    <!--DXMETADATA start type="PackageLink" packagename="core-topics" filetype="css" firstlinkattributes=" data-mshv2-stylesheet=""/template/packages/core-topics/topics.mshv2.css"" data-mshv1-stylesheet=""/template/packages/core-topics/topics.mshv1.css"" data-responsive-mobile=""template/packages/core-topics/topics.mobile.css"" data-responsive-tablet=""template/packages/core-topics/topics.tablet.css"""--><link rel="stylesheet" type="text/css" href="template/packages/core-topics/css/topics.css"  data-mshv2-stylesheet="/template/packages/core-topics/topics.mshv2.css" data-mshv1-stylesheet="/template/packages/core-topics/topics.mshv1.css" data-responsive-mobile="template/packages/core-topics/topics.mobile.css" data-responsive-tablet="template/packages/core-topics/topics.tablet.css"></link><!--DXMETADATA end-->
    <!--DXMETADATA start type="PackageLink" packagename="plugins-topics" filetype="script"--><script src="template/packages/plugins-topics/script/default/jquery-plugins.min.js" type="text/javascript"></script><!--DXMETADATA end-->
    <!--DXMETADATA start type="PackageLink" packagename="core-topics" filetype="script"--><script src="template/packages/core-topics/script/topics.min.js" type="text/javascript"></script><!--DXMETADATA end-->
    
    <!--DXMETADATA start type="TopicId" format="<meta name=""Microsoft.Help.Id"" content=""%%TopicId%%""/>" --><meta name="Microsoft.Help.Id" content="3fce8f38-461c-46d4-a2ef-e31c0b394999"/><!--DXMETADATA end-->
    <!--DXMETADATA start type="TocParentId" format="<meta name=""Microsoft.Help.TocParent"" content=""%%TocParentId%%""/>" --><meta name="Microsoft.Help.TocParent" content="20ec19b6-d22d-41ba-a05b-c2910bd90ae6"/><!--DXMETADATA end-->
    <!--DXMETADATA start type="TocOrdinal" format="<meta name=""Microsoft.Help.TocOrder"" content=""%%TocOrdinal%%""/>" --><meta name="Microsoft.Help.TocOrder" content="9"/><!--DXMETADATA end-->
    <meta name="Microsoft.Help.F1" content=""/>
    <meta name="Microsoft.Help.ContentType" content="Concepts" />
    <!--DXMETADATA start type="MshvKeywords" condition="helpversion:value=3" --><!--DXMETADATA end-->
    <!--DXMETADATA start type="MshvMetaTags" condition="helpversion:value=3" --><!--DXMETADATA end-->
    <!--DXMETADATA start type="Help3CatalogLocale" condition="helpversion:value=3" format="<meta name=""Microsoft.Help.Locale"" content=""%%Help3CatalogLocale%%"" />"--><!--DXMETADATA end-->
    <!--DXMETADATA start type="Help3CatalogLocale" condition="helpversion:value=3" format="<meta name=""Microsoft.Help.TopicLocale"" content=""%%Help3CatalogLocale%%"" />"--><!--DXMETADATA end-->
    <!--DXMETADATA start type="Stylesheets" --><link rel="stylesheet" type="text/css" href="stylesheets/hs-simpletab.css"></link><!--DXMETADATA end-->
    <!--DXMETADATA start type="StylePropertyValues" format="<style>%%StylePropertyValues%%</style>" --><style>.i-is-new .i-page-title-text::after, ul#i-dt-root li.i-is-new>a::after { content: "New" }
</style><!--DXMETADATA end-->
    <!--DXMETADATA start type="Scripts" --><!--DXMETADATA end-->
    <!--DXMETADATA start type="DesignTime"--><!--DXMETADATA end-->
    <!--DXMETADATA start type="Scrap" condition="communityenabled" name="_COMMUNITY_PROPERTIES" --><!--DXMETADATA end -->
    <!--DXMETADATA start type="CustomHeadContent" --><link rel="stylesheet" type="text/css" href="template/packages/core-web/css/SP.css">
<script type="text/javascript">
var currentHeader = function() {
    return "spjs";
}
</script>
<script src="template/packages/core-web/script/topNavigation.js"></script>
<script async="" src="https://www.googletagmanager.com/gtm.js?id=GTM-WT462SJ"></script>
<script>
        (function (w, d, s, l, i) {
            w[l] = w[l] || [];
            w[l].push({
                'gtm.start': new Date().getTime()
                , event: 'gtm.js'
            });
            var f = d.getElementsByTagName(s)[0]
                , j = d.createElement(s)
                , dl = l != 'dataLayer' ? '&l=' + l : '';
            j.async = true;
            j.src = 'https://www.googletagmanager.com/gtm.js?id=' + i + dl;
            f.parentNode.insertBefore(j, f);
        })(window, document, 'script', 'dataLayer', 'GTM-WT462SJ');
    </script>
<!--DXMETADATA end-->
</head>

<body>
    <div id="i-before-header-content" class="i-before-header-content">
        
    </div>

    <div id="i-header-container">
        <div id="i-header-content" class="i-header-content i-content-width-container">
            <!--DXMETADATA start type="LogoImage" --><!--DXMETADATA end-->
            <div class="i-project-title"><!--DXMETADATA start type="ProjectTitle" -->SpreadJS Documentation<!--DXMETADATA end--></div>
            <div class="i-search-container">
                <!--DXMETADATA start type="outputfileextension" format="<form action=""websearch%%outputfileextension%%"">" --><form action="websearch.html"><!--DXMETADATA end-->
                <input id="i-search" name="query"/>
                <input type="submit" id="i-search-button" value=""/>
                <!--DXMETADATA start type="Literal" value="</form>" --></form><!--DXMETADATA end-->
            </div>
            <!--DXMETADATA start type="outputfileextension" format="<a id=""i-index-button"" href=""webindex%%outputfileextension%%""></a>" --><a id="i-index-button" href="webindex.html"></a><!--DXMETADATA end-->
        </div>
    </div>
    
    <div class="i-content-width-container"><div class="i-busy-overlay"></div></div>

    <div id="i-breadcrumbs-outer-container" class="i-content-width-container"><!--DXMETADATA start type="Breadcrumbs" scrap="_BREADCRUMBS" --><div class="i-breadcrumbs-container">
<a href="overview.html">SpreadJS Documentation</a>
 / <a href="devguide.html">Developer's Guide</a>
 / <a href="features.html">Features</a>
 / <a href="interface.html">Manage User Interface</a>
 / <a href="formulas.html">Formulas</a>
 / Dynamic Array Formulas</div><!--DXMETADATA end --></div>

    

    <div id="i-actions-outer-container" class="i-content-width-container">
        <div id="i-actions-container">
            <div id="i-actions-content" class="i-fixed-to-top">
                <!-- Spacing --> <span class="i-toggle-all-sections i-function-link">
                <label class="i-collapse-all"><!--DXMETADATA start type="Phrase" name="COLLAPSE_ALL" -->Collapse All<!--DXMETADATA end--></label>
                <label class="i-expand-all" style="display: none;"><!--DXMETADATA start type="Phrase" name="EXPAND_ALL" -->Expand All<!--DXMETADATA end--></label>
            </span><!--DXMETADATA start type="Literal" condition="communityenabled" value="%%scrap:name=_COMMUNITY_DROPDOWN%%" --><!--DXMETADATA end -->
                <div class="i-in-this-topic-container">
                    <span class="i-action-group-heading"><!--DXMETADATA start type="Phrase" Name="IN_THIS_TOPIC" -->In This Topic<!--DXMETADATA end--></span>
                </div>
            </div>
        </div>
    </div>

    <div id="i-toc-outer-container" class="i-content-width-container">
        <div id="i-toc-container">
            <div id="i-toc-content" class="i-fixed-to-top">
                <div class="i-toc-content-scroll-container">
                    <div id="i-dynamic-toc-container"><ul id="i-dt-root"></ul></div>
                </div>
            </div>
        </div>
    </div>

    <div id="i-body-content-container" class="i-content-width-container">
        <div id="i-body-content" class="i-body-content">
            <div class="i-page-title"><div class="i-page-title-text"><!--DXMETADATA start type="ItemTitle" -->Dynamic Array Formulas<!--DXMETADATA end--></div></div>
            <div class="i-in-this-topic-container">
                <span class="i-action-group-heading"><!--DXMETADATA start type="Phrase" Name="IN_THIS_TOPIC" -->In This Topic<!--DXMETADATA end--></span>
            </div>
            <!--DXMETADATA start type="TopicSection" name="BodyText"--><p>SpreadJS provides extensive support for using dynamic array formulas in spreadsheets.</p>

<h3 id="a">Usage Scenario</h3>

<p>Dynamic Array Formulas return multiple results as output via automatic spilling and spanning to the cell range. This makes&nbsp;it much easier and quicker to work with array&nbsp;formulas in spreadsheets.</p>

<p>Dynamic array formulas are useful especially when:</p>

<ul>
    <li>Users want to implement effective utilization of data caches&nbsp;in their spreadsheets.</li>

    <li>Users want to build&nbsp;cache-friendly data structures that facilitate random access with low memory footprints (compactness). Generally, this is possible because dynamic arrays have only a small fixed additional overhead for storing information about the size and capacity.</li>
</ul>

<h3 id="b">UNIQUE Function</h3>

<p>The Unique function returns a list of all the unique values in a cell range.</p>

<p>For instance - The cell C4 contains the formula "=UNIQUE(A4:A15)" and returns only the unique customer names from the values in cell range A4 to A15. Based on the number of unique values, the dynamic array formula spills to the cell range C5 to C8 automatically, as shown below:</p>

<p><img style="MAX-WIDTH: 100%; HEIGHT: auto" border="0" alt="" src="images/spreadjsv13images/dynamic-1-uniq.png" />&nbsp;</p>

<h3 id="c">FILTER Function</h3>

<p>The FILTER function allows users to filter a cell range on the basis of the defined criteria. The Filter operation can be performed based on a single criterion or multiple criteria. In order to combine two or more filter conditions, users can use the " * " operator.</p>

<p>For instance - The cell F5 contains the formula "=FILTER(A5:D17, C5:C17=F1)". This formula filters the cell range A5 to D17 based on one filter criteria (when the cell range C5 to C17 matches the Product value in cell F1 i.e. Apple). As a result, all the values in the cell range A5 to D17 containing product as "Apple" will be displayed.</p>

<p><img style="MAX-WIDTH: 100%; HEIGHT: auto" border="0" alt="" src="images/spreadjsv13images/dynamic2.png" />&nbsp;</p>

<p>The cell F14 in the above image contains the formula "=FILTER(A5:D17, (C5:C17=F1)*(A5:A17=F2))". This formula filters the cell range A5 to D17 based on two filter conditions that are specified by the multiplication (*) operator. The first condition is the cell range C5 to C17 should match the Product value in cell F1 i.e. Apple and the second condition is the cell range A5 to A17 should match the region "East". As a result, all the values in the cell range A5 to D17 containing Product as "Apple" and Region as "East" will be displayed.</p>

<h3 id="d">RANDARRAY Function</h3>

<p>The RANDARRAY function returns an array of random numeric values. Users can specify the number of rows and columns, minimum and maximum values and indicate whether to return integers or decimal values.</p>

<p>For instance- The cell A8 in the following image contains the formula "=RANDARRAY(5,3)" and returns a random set of values between 0 and 1.</p>

<p><img style="MAX-WIDTH: 100%; HEIGHT: auto" border="0" alt="" src="images/spreadjsv13images/dynamic5.png" /></p>

<h3 id="e">SORT Function</h3>

<p>The SORT function sorts the data in a cell range or an array. The results of this function spill into the resultant range with a dynamic array of values arranged in the ascending (increasing) or descending (decreasing) order. If the sort order is not specified, then by default, the values are sorted alphabetically in the ascending order.</p>

<p><br />
For instance- The cell D4 contains the formula "=SORT(A4:A15)" and returns the customer names sorted in the increasing order.</p>

<p><img style="MAX-WIDTH: 100%; HEIGHT: auto" border="0" alt="" src="images/spreadjsv13images/dynamic-1-sort.png" />&nbsp;</p>

<p>In case you want to sort all the unique values in the range A4 to A15, you can either apply the sort function on the unique list displayed in the column C4 or you can also combine both the functions SORT and UNIQUE into a single formula.</p>

<p>For instance, the cell E4 contains the formula "=SORT(C4#)" where # indicates a list. This formula will sort the list of values in column C (where cell C4 already contains the UNIQUE formula "=UNIQUE(A4:A15)") and displays the results in column E.</p>

<p>Alternatively, you can also combine both the functions SORT and UNIQUE. For instance, the cell F4 in the following image contains the formula "=SORT(UNIQUE(A4:A15))" which returns all the unique values in the range A4:A15 sorted alphabetically.</p>

<p><img style="MAX-WIDTH: 100%; HEIGHT: auto" border="0" alt="" src="images/spreadjsv13images/dynamic-sortunique.png" /></p>

<h3 id="f">SORT BY Function</h3>

<p>The SORTBY function sorts the contents of a cell range or an array on the basis of the values present in a corresponding range or array.</p>

<p>For instance - The cell G4 contains the formula "=SORTBY(A4:B15,B4:B15)". This function sorts the cell range A4 to B15 based on another cell range B4 to B15 and returns the customer names displayed along with their ages sorted in the increasing order.</p>

<p><img style="MAX-WIDTH: 100%; HEIGHT: auto" border="0" alt="" src="images/spreadjsv13images/dynamic-1-sortby.png" /></p>

<h3 id="g">SEQUENCE Function</h3>

<p>The SEQUENCE function returns a list of sequential numbers in an array in the ascending order.</p>

<p>For instance - The cell A2 contains the formula "=SEQUENCE(4,5)" and returns an array with values spilled to a cell range containing four rows and five columns displaying numbers in the sequence 1, 2, 3, 4 upto 20.</p>

<p><img style="MAX-WIDTH: 100%; HEIGHT: auto" border="0" alt="" src="images/spreadjsv13images/dynamic4.png" /></p>

<h3 id="h">SINGLE Function</h3>

<p>The SINGLE function returns a single value, a single cell range or an error using the&nbsp;implicit intersection logic.</p>

<p>For instance - The cell A15 contains the formula "=SINGLE(A15:E15)" and returns the result "C" in the cell C16 by evaluating the intersection of the rows and columns in the cell range A15 to E15.</p>

<p><img style="MAX-WIDTH: 100%; HEIGHT: auto" border="0" alt="" src="images/spreadjsv13images/dynamic6.png" /></p>

<h3 id="i">Spilled Array Formulas</h3>

<p>Working with generic formulas in spreadsheets is a cumbersome task because users need to manually copy the formulas to every cell where they want the result to be calculated. When a cell contains a dynamic array formula, the elements of the array spill into the adjacent empty cells and multiple values are returned. Unlike generic arrays, dynamic arrays automatically resize when the data is inserted or removed from the source range.</p>

<p><strong>Spilling</strong> - When all the formulas return multiple values (in an array), the neighboring cells will be populated with the results (calculated data). This behavior is called spilling. Any formula that possesses the potential to return multiple results can be referred to as a dynamic array formula.</p>

<p><strong>Spilled Array Formulas</strong>- Formulas that return multiple results across the cell range in the spreadsheet, and are successfully spilling, are called spilled array formulas.&nbsp;</p>

<p><strong>Spill Range Operator</strong> - The spill range operator (#) can be used to reference the entire spill range. #Spill errors are returned when a formula returns multiple results.</p>

<h3 id="j">Enable Dynamic Array Functions</h3>

<p>In order to use dynamic array functions in spreadsheets, users first need to enable dynamic array&nbsp;following code snippet&nbsp;.</p>

<div class="i-filtered-content-JavaScript" data-title="JavaScript" data-itemid="JavaScript">
    <table class="i-syntax-table">
        <tbody>
            <tr>
                <th>JavaScript</th>

                <th>
                    <div class="i-copy-code-wrapper">
                        <span class="i-copy-code">Copy Code</span>
                    </div>
                </th>
            </tr>

            <tr>
                <td class="i-code" colspan="2">// Enable Dynamic Array<br />
                spread.options.allowDynamicArray = true;</td>
            </tr>
        </tbody>
    </table>
</div>

<p>&nbsp;</p>

<h3 id="k">Using Code</h3>

<p>The following example code demonstrates how the dynamic array functions are used in the spreadsheet.</p>

<div class="i-filtered-content-JavaScript" data-title="JavaScript" data-itemid="JavaScript">
    <table class="i-syntax-table">
        <tbody>
            <tr>
                <th>JavaScript</th>

                <th>
                    <div class="i-copy-code-wrapper">
                        <span class="i-copy-code">Copy Code</span>
                    </div>
                </th>
            </tr>

            <tr>
                <td class="i-code" colspan="2">
                    <p>// Initializing Spread<br />
                    var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 4 });<br /></p>

                    <p>// Get the activesheet<br />
                    var activeSheet = spread.getActiveSheet();</p>

                    <p><br />
                    // Enable Dynamic Array<br />
                    spread.options.allowDynamicArray = true;</p>

                    <p>activeSheet.FrozenRowCount = 1;<br />
                    activeSheet.setText(0, 0, "Dynamic Array Functions");<br />
                    activeSheet.getCell(0, 0).backColor("LightGray");<br />
                    activeSheet.addSpan(0, 0, 1, 3, GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>// Setting Data in Cells of Sheet[0]<br />
                    activeSheet.setText(2, 0, "Customer's Name");<br />
                    activeSheet.getCell(2, 0).backColor("LightGray");<br />
                    activeSheet.setText(3, 0, "Larry");<br />
                    activeSheet.setText(4, 0, "Safeway");<br />
                    activeSheet.setText(5, 0, "Safeway");<br />
                    activeSheet.setText(6, 0, "Raley");<br />
                    activeSheet.setText(7, 0, "Vallarta");<br />
                    activeSheet.setText(8, 0, "Safeway");<br />
                    activeSheet.setText(9, 0, "Raley");<br />
                    activeSheet.setText(10, 0, "Larry");<br />
                    activeSheet.setText(11, 0, "Gilbert");<br />
                    activeSheet.setText(12, 0, "Larry");<br />
                    activeSheet.setText(13, 0, "Larry");<br />
                    activeSheet.setText(14, 0, "Raley");<br />
                    activeSheet.setColumnWidth(0, 120.0, GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>activeSheet.setText(2, 1, "Age");<br />
                    activeSheet.getCell(2, 1).backColor("LightGray");<br />
                    activeSheet.setText(3, 1, "32");<br />
                    activeSheet.setText(4, 1, "23");<br />
                    activeSheet.setText(5, 1, "23");<br />
                    activeSheet.setText(6, 1, "39");<br />
                    activeSheet.setText(7, 1, "18");<br />
                    activeSheet.setText(8, 1, "23");<br />
                    activeSheet.setText(9, 1, "39");<br />
                    activeSheet.setText(10, 1, "32");<br />
                    activeSheet.setText(11, 1, "19");<br />
                    activeSheet.setText(12, 1, "32");<br />
                    activeSheet.setText(13, 1, "32");<br />
                    activeSheet.setText(14, 1, "39");<br />
                    activeSheet.setColumnWidth(1, 50.0, GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>// Setting "Unique" Formula<br />
                    activeSheet.setText(2, 2, "Unique List");<br />
                    activeSheet.getCell(2, 2).backColor("LightBlue");<br />
                    activeSheet.setFormula(3, 2, "UNIQUE(A4:A15)");<br />
                    activeSheet.setColumnWidth(2, 90.0, GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>// Setting "Sort" Formula<br />
                    activeSheet.setText(2, 3, "Sort");<br />
                    activeSheet.getCell(2, 3).backColor("LightBlue");<br />
                    activeSheet.setFormula(3, 3, "SORT(A4:A15)");<br />
                    activeSheet.setColumnWidth(3, 90.0, GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>// Setting "Sort" Formula for Unique list<br />
                    activeSheet.setText(2, 4, "Sort Unique");<br />
                    activeSheet.getCell(2, 4).backColor("LightBlue");<br />
                    activeSheet.setFormula(3, 4, "SORT(C4#)");<br />
                    activeSheet.setColumnWidth(4, 90.0, GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>// Setting "Sort+Unique" Formula together<br />
                    activeSheet.setText(2, 5, "Sort Unique");<br />
                    activeSheet.getCell(2, 5).backColor("LightBlue");<br />
                    activeSheet.setFormula(3, 5, "SORT(UNIQUE(A4:A15)");<br />
                    activeSheet.setColumnWidth(5, 90.0, GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>// Setting "SortBy" Formula wherein we sort Range A4:B15 based on the values in a corresponding range B4:B15<br />
                    activeSheet.setText(2, 6, "SortBy");<br />
                    activeSheet.getCell(2, 6).backColor("LightBlue");<br />
                    activeSheet.setFormula(3, 6, "SORTBY(A4:B15, B4:B15)");<br />
                    activeSheet.setColumnWidth(6, 90.0, GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>// get the second sheet<br />
                    var activeSheet = spread.getSheet(1);</p>

                    <p>// Setting Data in Cells of Sheet[1]<br />
                    for (var i = 0; i &lt; 9; i++)<br />
                    &nbsp;&nbsp;&nbsp;&nbsp; activeSheet.setColumnWidth(i, 70.0, GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>activeSheet.setText(3, 0, "Region");<br />
                    activeSheet.getCell(3, 0).backColor("LightGray");<br />
                    activeSheet.setText(4, 0, "East");<br />
                    activeSheet.setText(5, 0, "North");<br />
                    activeSheet.setText(6, 0, "Wast");<br />
                    activeSheet.setText(7, 0, "Sast");<br />
                    activeSheet.setText(8, 0, "East");<br />
                    activeSheet.setText(9, 0, "East");<br />
                    activeSheet.setText(10, 0, "West");<br />
                    activeSheet.setText(11, 0, "South");<br />
                    activeSheet.setText(12, 0, "North");<br />
                    activeSheet.setText(13, 0, "North");<br />
                    activeSheet.setText(14, 0, "East");<br />
                    activeSheet.setText(15, 0, "South");<br />
                    activeSheet.setText(16, 0, "West");</p>

                    <p>activeSheet.setText(3, 1, "Sales Rep");<br />
                    activeSheet.getCell(3, 1).backColor("LightGray");<br />
                    activeSheet.setText(4, 1, "Tom");<br />
                    activeSheet.setText(5, 1, "Fred");<br />
                    activeSheet.setText(6, 1, "Amy");<br />
                    activeSheet.setText(7, 1, "Sal");<br />
                    activeSheet.setText(8, 1, "Hector");<br />
                    activeSheet.setText(9, 1, "Xi");<br />
                    activeSheet.setText(10, 1, "Amy");<br />
                    activeSheet.setText(11, 1, "Sal");<br />
                    activeSheet.setText(12, 1, "Fred");<br />
                    activeSheet.setText(13, 1, "Tom");<br />
                    activeSheet.setText(14, 1, "Hector");<br />
                    activeSheet.setText(15, 1, "Sravan");<br />
                    activeSheet.setText(16, 1, "Xi");</p>

                    <p>activeSheet.setText(3, 2, "Product");<br />
                    activeSheet.getCell(3, 2).backColor("LightGray");<br />
                    activeSheet.setText(4, 2, "Apple");<br />
                    activeSheet.setText(5, 2, "Grape");<br />
                    activeSheet.setText(6, 2, "Pear");<br />
                    activeSheet.setText(7, 2, "Banana");<br />
                    activeSheet.setText(8, 2, "Apple");<br />
                    activeSheet.setText(9, 2, "Banana");<br />
                    activeSheet.setText(10, 2, "Banana");<br />
                    activeSheet.setText(11, 2, "Pear");<br />
                    activeSheet.setText(12, 2, "Apple");<br />
                    activeSheet.setText(13, 2, "Grape");<br />
                    activeSheet.setText(14, 2, "Grape");<br />
                    activeSheet.setText(15, 2, "Apple");<br />
                    activeSheet.setText(16, 2, "Grape");</p>

                    <p>activeSheet.setText(3, 3, "Units");<br />
                    activeSheet.getCell(3, 3).backColor("LightGray");<br />
                    activeSheet.setText(4, 3, "6380");<br />
                    activeSheet.setText(5, 3, "2344");<br />
                    activeSheet.setText(6, 3, "3434");<br />
                    activeSheet.setText(7, 3, "5461");<br />
                    activeSheet.setText(8, 3, "2341");<br />
                    activeSheet.setText(9, 3, "3234");<br />
                    activeSheet.setText(10, 3, "6532");<br />
                    activeSheet.setText(11, 3, "7323");<br />
                    activeSheet.setText(12, 3, "2334");<br />
                    activeSheet.setText(13, 3, "8734");<br />
                    activeSheet.setText(14, 3, "1932");<br />
                    activeSheet.setText(15, 3, "7682");<br />
                    activeSheet.setText(16, 3, "3293");</p>

                    <p>activeSheet.setText(0, 4, "Product:");<br />
                    activeSheet.getCell(0, 4).backColor("LightGray");<br />
                    activeSheet.setText(0, 5, "Apple");<br />
                    activeSheet.setText(1, 4, "Region:");<br />
                    activeSheet.getCell(1, 4).backColor("LightGray");<br />
                    activeSheet.setText(1, 5, "East");<br />
                    activeSheet.setText(2, 5, "Filtering performed on one Criteria");<br />
                    activeSheet.getCell(2, 5).backColor("LightBlue");<br />
                    activeSheet.addSpan(2, 5, 1, 4, GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>activeSheet.setText(3, 5, "Region");<br />
                    activeSheet.getCell(3, 5).backColor("LightGray");<br />
                    activeSheet.setText(3, 6, "Sales Rep");<br />
                    activeSheet.getCell(3, 6).backColor("LightGray");<br />
                    activeSheet.setText(3, 7, "Product");<br />
                    activeSheet.getCell(3, 7).backColor("LightGray");<br />
                    activeSheet.setText(3, 8, "Units");<br />
                    activeSheet.getCell(3, 8).backColor("LightGray");</p>

                    <p>// Setting "Filter" Formula( with one condition) wherein we filter range A5:D21<br />
                    // based upon criteria wherein range C5:C21 is equal to value in cell F1<br />
                    activeSheet.setFormula(4, 5, "FILTER(A5:D21, C5:C21=F1)", GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>activeSheet.setText(12, 5, "Region");<br />
                    activeSheet.getCell(12, 5).backColor("LightGray");<br />
                    activeSheet.setText(12, 6, "Sales Rep");<br />
                    activeSheet.getCell(12, 6).backColor("LightGray");<br />
                    activeSheet.setText(12, 7, "Product");<br />
                    activeSheet.getCell(12, 7).backColor("LightGray");<br />
                    activeSheet.setText(12, 8, "Units");<br />
                    activeSheet.getCell(12, 8).backColor("LightGray");<br />
                    activeSheet.setText(11, 5, "Filtering performed on two Criteria");<br />
                    activeSheet.getCell(11, 5).backColor("LightBlue");<br />
                    activeSheet.addSpan(11, 5, 1, 4, GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>// Setting "Filter" Formula( with two conditions) wherein we filter range A5:D21<br />
                    // based upon criteria wherein range C5:C21 is equal to value in cell F1<br />
                    // and range A5:A21 is equal to value in cell F2<br />
                    activeSheet.setFormula(13, 5, "FILTER(A5:D21, (C5:C21=F1)*(A5:A21=F2))", GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>// get the second sheet<br />
                    var activeSheet = spread.getSheet(2);<br />
                    for (var i = 0; i &lt; 7; i++)<br />
                    activeSheet.setColumnWidth(i, 130.0, GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>// Setting "Sequence" FormulaactiveSheet.Columns[0, 7].Width = 130;<br />
                    activeSheet.setText(0, 0, "SEQUENCE(4,5) Function");<br />
                    activeSheet.addSpan(0, 0, 1, 2, GC.Spread.Sheets.SheetArea.viewport);<br />
                    activeSheet.getCell(0, 0).backColor("SkyBlue");<br />
                    activeSheet.setFormula(1, 0, "SEQUENCE(4,5)", GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>// Setting "RandArray" Formula<br />
                    activeSheet.setText(6, 0, "RANDARRAY(5,3) Function");<br />
                    activeSheet.addSpan(6, 0, 1, 2, GC.Spread.Sheets.SheetArea.viewport);<br />
                    activeSheet.getCell(6, 0).backColor("SkyBlue");<br />
                    activeSheet.setFormula(7, 0, "RANDARRAY(5,3)", GC.Spread.Sheets.SheetArea.viewport);</p>

                    <p>// Setting "Single" Formula<br />
                    activeSheet.setText(13, 0, "SINGLE Function is represented by @ i.e. <a href="mailto:=@(A15:E15">=@(A15:E15</a>)");<br />
                    activeSheet.addSpan(13, 0, 1, 4, GC.Spread.Sheets.SheetArea.viewport);<br />
                    activeSheet.getCell(13, 0).backColor("SkyBlue");<br />
                    activeSheet.setValue(14, 0, "A");<br />
                    activeSheet.setValue(14, 1, "B");<br />
                    activeSheet.setValue(14, 2, "C");<br />
                    activeSheet.setValue(14, 3, "D");<br />
                    activeSheet.setValue(14, 4, "E");<br />
                    activeSheet.setFormula(15, 2, "@(A15:E15)", GC.Spread.Sheets.SheetArea.viewport);</p>
                </td>
            </tr>
        </tbody>
    </table>
</div><!--DXMETADATA end-->
            <a name="seealsobookmark"></a>
            <!--DXMETADATA start type="FilteredItemList" scrap="CATEGORISED_LINKS" namespace="linkcategory" source="Item" filter="" NoHeader="True" NoFooter="True" format="%%replaceinquotes:value=false%%%%scrap:name=_COLLAPSIBLE_HEADER,idprefix=seealso,caption=""%%phrase:name=SeeAlso%%%%designlist:tagidentifier=seealso,itemtype=See Also%%""%%%%filtereditemlist%%</div>"--><!--DXMETADATA end-->
            
            <div id="i-footer-content" class="i-footer-content">
                <!--DXMETADATA start type="Scrap" condition="communityenabled" name="_COMMUNITY_FOOTER" --><!--DXMETADATA end -->
<!--DXMETADATA start type="Variable" name="CopyrightNotice" format="<p>&nbsp;</p><p>&nbsp;</p><hr style=""height: 1px"" /><p>%%variable%%</p>" --><p>&nbsp;</p><p>&nbsp;</p><hr style="height: 1px" /><p><p>Copyright © 2019 GrapeCity, Inc. All rights reserved.</p>
<p><a href="https://www.grapecity.com/en/forums/winforms-edition" target="_blank">Product Support Forum</a> | <a href="javascript:window.print()">Print this page</a></p>
<ul class="social">
<li><a href="https://www.facebook.com/GrapeCityUS/" target="_blank" class="facebook">&nbsp;</a></li>
<li><a href="https://twitter.com/GrapeCityUS" target="_blank" class="twitter">&nbsp;</a></li>
<li><a href="https://www.linkedin.com/company/grapecity" target="_blank" class="linkedin">&nbsp;</a></li>
</ul>
</p><!--DXMETADATA end -->
<!--DXMETADATA start type="Variable" name="FeedbackLink" format="" --><!--DXMETADATA end-->
            </div>
        </div>
    </div>
        
    <script type="text/javascript">
        $(function () {
            var documentInstance = new Innovasys.Content.Document(document.body);
            documentInstance.load();
        });
    </script>
</body>
</html>
